IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[csp_rptMetric_PCROAwardsToBeActivatedDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[csp_rptMetric_PCROAwardsToBeActivatedDetails]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  
-- csp_rptMetric_PCROAwardsToBeActivatedDetails 'WEEK','12/28/2008',2,'12/28/2009',8,0,NULL,NULL

CREATE  PROCEDURE [dbo].[csp_rptMetric_PCROAwardsToBeActivatedDetails]   
(    
  @PeriodType varchar(50)      
, @PeriodStartDate smalldatetime      
, @Institution INT    
--new parameters for metrics details breakdown report    
, @PeriodEndDate smalldatetime = NULL   
, @Department INT = NULL    
, @Unit INT = NULL   
, @PrincipalInvestigator varchar(50)=NULL    
, @Sponsor  varchar(50) = NULL  
)    
AS    
 
BEGIN 
SELECT
B.PeriodType,
B.PeriodStartDate,
B.Institution,
B.ProposalNumber,
B.PIName,
B.ChiefCode,
B.Sponsor,
B.SponsorID,
B.ProposalStatus,
B.ExecutedDate,
B.UploadType,
B.UploadDate,
B.UploadedBy,
B.CoversheetUploadDate,
B.CoversheetUploadedBy,
B.PostAwardManager,
B.LastActivityComment,
B.LastActivityDate,
B.LastActivityEnterer

FROM  INSIGHT_RPT_DB.dbo.FwkDomainOrganization A
INNER JOIN rptMetric_PCROAwardsToBeActivated  B
on A.chiefcode = B.chiefCode AND RTRIM(LTRIM(A.Institution))= RTRIM(LTRIM(B.Institution))
AND B.PeriodType = @PeriodType
AND A.institutionid= @Institution
AND (A.DepartmentId = @Department OR @Department =0 OR @Department is NULL)
AND ( A.UnitId =@Unit OR @Unit=0 OR @Unit IS NULL) 
AND  B.PeriodStartDate BETWEEN @PeriodStartDate AND @PeriodEndDate
AND (B.PIName = @PrincipalInvestigator OR @PrincipalInvestigator IS NULL OR @PrincipalInvestigator='')  
AND  (B.Sponsor = @Sponsor OR @Sponsor IS NULL OR @Sponsor='')


END 

GO

